#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
百度地图坐标转换为腾讯地图坐标的迁移脚本
将数据库中的BD-09坐标转换为GCJ-02坐标

使用说明：
1. 安装依赖：pip install pymysql requests
2. 运行脚本：python coordinate_migration.py
3. 脚本会自动备份数据并进行转换
"""

import pymysql
import requests
import json
import time
import logging
from datetime import datetime
from typing import List, Dict, Optional, Tuple

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('coordinate_migration.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

# 数据库配置
DB_CONFIG = {
    'host': '1.92.133.195',
    'port': 3306,
    'user': 'weixin',
    'password': 'k5Ykx2WSmjHJaPFs',
    'database': 'weixin',
    'charset': 'utf8mb4'
}

# 腾讯地图API配置
TENCENT_MAP_KEY = 'N6PBZ-UYIY3-3773L-O2DDK-FGIBH-VLF7M'
COORDINATE_CONVERT_URL = 'https://apis.map.qq.com/ws/coord/v1/translate'

# 需要转换的表和字段配置
TABLES_TO_CONVERT = [
    {
        'table': 'venues',
        'id_field': 'id',
        'lat_field': 'latitude',
        'lng_field': 'longitude'
    },
    {
        'table': 'activities', 
        'id_field': 'id',
        'lat_field': 'latitude',
        'lng_field': 'longitude'
    }
]

class CoordinateConverter:
    """坐标转换器"""
    
    def __init__(self):
        self.connection = None
        self.total_converted = 0
        self.failed_conversions = []
        
    def connect_database(self) -> bool:
        """连接数据库"""
        try:
            self.connection = pymysql.connect(**DB_CONFIG)
            logger.info("数据库连接成功")
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {e}")
            return False
    
    def close_database(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            logger.info("数据库连接已关闭")
    
    def backup_table(self, table_name: str) -> bool:
        """备份表数据"""
        try:
            cursor = self.connection.cursor()
            backup_table_name = f"{table_name}_backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
            
            # 创建备份表
            cursor.execute(f"CREATE TABLE {backup_table_name} AS SELECT * FROM {table_name}")
            self.connection.commit()
            
            logger.info(f"表 {table_name} 备份完成，备份表名: {backup_table_name}")
            return True
        except Exception as e:
            logger.error(f"备份表 {table_name} 失败: {e}")
            return False
        finally:
            cursor.close()
    
    def get_coordinates_from_table(self, table_config: Dict) -> List[Dict]:
        """从表中获取坐标数据"""
        try:
            cursor = self.connection.cursor()
            query = f"""
                SELECT {table_config['id_field']}, 
                       {table_config['lat_field']}, 
                       {table_config['lng_field']}
                FROM {table_config['table']} 
                WHERE {table_config['lat_field']} IS NOT NULL 
                  AND {table_config['lng_field']} IS NOT NULL
                  AND {table_config['lat_field']} != 0 
                  AND {table_config['lng_field']} != 0
            """
            
            cursor.execute(query)
            results = cursor.fetchall()
            
            coordinates = []
            for row in results:
                coordinates.append({
                    'id': row[0],
                    'latitude': float(row[1]),
                    'longitude': float(row[2])
                })
            
            logger.info(f"从表 {table_config['table']} 获取到 {len(coordinates)} 条坐标记录")
            return coordinates
            
        except Exception as e:
            logger.error(f"获取表 {table_config['table']} 坐标数据失败: {e}")
            return []
        finally:
            cursor.close()
    
    def convert_bd09_to_gcj02(self, coordinates: List[Dict]) -> List[Dict]:
        """
        批量将BD-09坐标转换为GCJ-02坐标
        使用腾讯地图坐标转换API
        """
        if not coordinates:
            return []
        
        # 腾讯地图API一次最多转换100个坐标点
        batch_size = 100
        converted_results = []
        
        for i in range(0, len(coordinates), batch_size):
            batch = coordinates[i:i + batch_size]
            
            # 构造API请求参数
            locations = []
            for coord in batch:
                locations.append(f"{coord['latitude']},{coord['longitude']}")
            
            params = {
                'locations': ';'.join(locations),
                'type': 1,  # 1: BD-09转GCJ-02
                'key': TENCENT_MAP_KEY
            }
            
            try:
                logger.info(f"正在转换第 {i//batch_size + 1} 批坐标 (共 {len(batch)} 个)")
                response = requests.get(COORDINATE_CONVERT_URL, params=params, timeout=30)
                response.raise_for_status()
                
                result = response.json()
                
                if result.get('status') == 0:
                    # 转换成功
                    converted_locations = result.get('locations', [])
                    
                    for j, location in enumerate(converted_locations):
                        if j < len(batch):
                            converted_results.append({
                                'id': batch[j]['id'],
                                'original_lat': batch[j]['latitude'],
                                'original_lng': batch[j]['longitude'],
                                'converted_lat': location['lat'],
                                'converted_lng': location['lng']
                            })
                else:
                    logger.error(f"腾讯地图API返回错误: {result.get('message', '未知错误')}")
                    # 如果API失败，记录失败的坐标
                    for coord in batch:
                        self.failed_conversions.append(coord)
                
                # API调用间隔，避免频率限制
                time.sleep(0.1)
                
            except requests.exceptions.RequestException as e:
                logger.error(f"调用腾讯地图API失败: {e}")
                # 记录失败的坐标
                for coord in batch:
                    self.failed_conversions.append(coord)
            except Exception as e:
                logger.error(f"坐标转换过程出错: {e}")
                for coord in batch:
                    self.failed_conversions.append(coord)
        
        logger.info(f"坐标转换完成，成功转换 {len(converted_results)} 个，失败 {len(self.failed_conversions)} 个")
        return converted_results
    
    def update_coordinates_in_table(self, table_config: Dict, converted_coordinates: List[Dict]) -> bool:
        """更新表中的坐标数据"""
        if not converted_coordinates:
            return True
        
        try:
            cursor = self.connection.cursor()
            
            # 批量更新坐标
            update_query = f"""
                UPDATE {table_config['table']} 
                SET {table_config['lat_field']} = %s, 
                    {table_config['lng_field']} = %s
                WHERE {table_config['id_field']} = %s
            """
            
            update_data = []
            for coord in converted_coordinates:
                update_data.append((
                    coord['converted_lat'],
                    coord['converted_lng'], 
                    coord['id']
                ))
            
            cursor.executemany(update_query, update_data)
            self.connection.commit()
            
            logger.info(f"表 {table_config['table']} 坐标更新完成，更新了 {len(converted_coordinates)} 条记录")
            self.total_converted += len(converted_coordinates)
            return True
            
        except Exception as e:
            logger.error(f"更新表 {table_config['table']} 坐标失败: {e}")
            self.connection.rollback()
            return False
        finally:
            cursor.close()
    
    def verify_conversion(self, table_config: Dict, sample_size: int = 5) -> bool:
        """验证转换结果"""
        try:
            cursor = self.connection.cursor()
            
            # 随机抽取几条记录验证
            query = f"""
                SELECT {table_config['id_field']}, 
                       {table_config['lat_field']}, 
                       {table_config['lng_field']}
                FROM {table_config['table']} 
                WHERE {table_config['lat_field']} IS NOT NULL 
                  AND {table_config['lng_field']} IS NOT NULL
                ORDER BY RAND() 
                LIMIT {sample_size}
            """
            
            cursor.execute(query)
            results = cursor.fetchall()
            
            logger.info(f"表 {table_config['table']} 验证样本:")
            for row in results:
                logger.info(f"  ID: {row[0]}, 纬度: {row[1]}, 经度: {row[2]}")
            
            return True
            
        except Exception as e:
            logger.error(f"验证表 {table_config['table']} 失败: {e}")
            return False
        finally:
            cursor.close()
    
    def migrate_coordinates(self) -> bool:
        """执行坐标迁移"""
        logger.info("开始坐标迁移任务")
        
        # 连接数据库
        if not self.connect_database():
            return False
        
        try:
            for table_config in TABLES_TO_CONVERT:
                table_name = table_config['table']
                logger.info(f"开始处理表: {table_name}")
                
                # 1. 备份表
                if not self.backup_table(table_name):
                    logger.error(f"表 {table_name} 备份失败，跳过此表")
                    continue
                
                # 2. 获取坐标数据
                coordinates = self.get_coordinates_from_table(table_config)
                if not coordinates:
                    logger.warning(f"表 {table_name} 没有需要转换的坐标数据")
                    continue
                
                # 3. 转换坐标
                converted_coordinates = self.convert_bd09_to_gcj02(coordinates)
                
                # 4. 更新数据库
                if converted_coordinates:
                    self.update_coordinates_in_table(table_config, converted_coordinates)
                
                # 5. 验证结果
                self.verify_conversion(table_config)
                
                logger.info(f"表 {table_name} 处理完成")
            
            # 输出总结
            logger.info(f"坐标迁移任务完成!")
            logger.info(f"总计转换坐标: {self.total_converted} 个")
            if self.failed_conversions:
                logger.warning(f"转换失败: {len(self.failed_conversions)} 个")
                logger.warning("失败的坐标记录已保存到日志中")
            
            return True
            
        except Exception as e:
            logger.error(f"坐标迁移过程出错: {e}")
            return False
        finally:
            self.close_database()
    
    def generate_report(self):
        """生成迁移报告"""
        report_filename = f"migration_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.txt"
        
        with open(report_filename, 'w', encoding='utf-8') as f:
            f.write("坐标迁移报告\n")
            f.write("=" * 50 + "\n")
            f.write(f"迁移时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
            f.write(f"总计转换: {self.total_converted} 个坐标\n")
            f.write(f"转换失败: {len(self.failed_conversions)} 个坐标\n")
            f.write("\n")
            
            if self.failed_conversions:
                f.write("失败的坐标记录:\n")
                f.write("-" * 30 + "\n")
                for i, coord in enumerate(self.failed_conversions, 1):
                    f.write(f"{i}. ID: {coord['id']}, 纬度: {coord['latitude']}, 经度: {coord['longitude']}\n")
        
        logger.info(f"迁移报告已生成: {report_filename}")


def main():
    """主函数"""
    print("百度地图坐标转换为腾讯地图坐标迁移脚本")
    print("=" * 50)
    
    # 确认执行
    confirm = input("此操作将修改数据库中的坐标数据，确定要继续吗？(y/N): ")
    if confirm.lower() != 'y':
        print("迁移已取消")
        return
    
    # 执行迁移
    converter = CoordinateConverter()
    success = converter.migrate_coordinates()
    
    # 生成报告
    converter.generate_report()
    
    if success:
        print("\n迁移完成！请检查日志文件获取详细信息。")
    else:
        print("\n迁移过程中出现错误，请检查日志文件。")


if __name__ == "__main__":
    main() 